from sqlalchemy.orm import Session
from schemas import schemas
from schemas import models
from typing import List, Optional

# 用户相关CRUD操作
def create_user(db: Session, user: schemas.UserCreate) -> models.User:
    # 将密码转为hash_password
    from passlib.context import CryptContext

    pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
    hashed_password = pwd_context.hash(user.password)
    
    user_data = user.dict()
    user_data["hashed_password"] = hashed_password
    del user_data["password"]
    # 创建用户的主邮箱
    email = user_data.pop("email")
    primary_email = models.Email(email=email, is_primary=True)
    user_data["emails"] = [primary_email]
    db_user = models.User(**user_data)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

def get_user(db: Session, user_id: int) -> Optional[models.User]:
    return db.query(models.User).filter(models.User.id == user_id).first()

def get_user_by_username(db: Session, username: str) -> Optional[models.User]:
    return db.query(models.User).filter(models.User.username == username).first()

def get_users(db: Session, skip: int = 0, limit: int = 100) -> List[models.User]:
    return db.query(models.User).offset(skip).limit(limit).all()

def get_list_data(db: Session, skip, limit):
    total = db.query(models.User).count()
    users = db.query(models.User).offset(skip).limit(limit).all()
    
    result = []
    for user in users:
        user_data = {
            "id": user.id,
            "username": user.username,
            "is_active": user.is_active,
            "points": user.points,
            "emails": [{"email": email.email, "is_primary": email.is_primary} for email in user.emails],
            "subscriptions": db.query(models.Subscription.id, models.Subscription.name)
                               .join(models.UserSubscription)
                               .filter(models.UserSubscription.user_id == user.id)
                               .all()
        }
        result.append(user_data)
    
    return {"total": total, "list": result}
def get_point_log_by_id(db: Session, log_id: int) -> Optional[models.PointLog]:
    return db.query(models.PointLog).filter(models.PointLog.id == log_id).first()
def get_user_point_logs(db: Session, user_id: int, skip: int = 0, limit: int = 10) -> dict:
    total = db.query(models.PointLog).filter(models.PointLog.user_id == user_id).count()
    logs = db.query(models.PointLog).filter(models.PointLog.user_id == user_id).order_by(models.PointLog.created_at.desc()).offset(skip).limit(limit).all()
    
    result = []
    for log in logs:
        log_data = {
            "id": log.id,
            "points_change": log.points_change,
            "reason": log.reason,
            "created_at": log.created_at
        }
        result.append(log_data)
    
    return {"total": total, "list": result}

def get_point_logs(db: Session, skip: int = 0, limit: int = 10) -> dict:
    total = db.query(models.PointLog).count()
    logs = db.query(models.PointLog).offset(skip).limit(limit).all()
    
    result = []
    for log in logs:
        log_data = {
            "id": log.id,
            "user_id": log.user_id,
            "points_change": log.points_change,
            "reason": log.reason,
            "created_at": log.created_at
        }
        result.append(log_data)
    
    return {"total": total, "list": result}

def add_user_subscription(db: Session, user_id: int, subscription_id: int) -> Optional[models.User]:
    db_user = db.query(models.User).filter(models.User.id == user_id).first()
    db_subscription = db.query(models.Subscription).filter(models.Subscription.id == subscription_id).first()
    
    if db_user and db_subscription:
        if db_subscription not in db_user.subscriptions:
            db_user.subscriptions.append(db_subscription)
            db.commit()
            db.refresh(db_user)
        return db_user
    return None

def remove_user_subscription(db: Session, user_id: int, subscription_id: int) -> Optional[models.User]:
    db_user = db.query(models.User).filter(models.User.id == user_id).first()
    db_subscription = db.query(models.Subscription).filter(models.Subscription.id == subscription_id).first()
    
    if db_user and db_subscription:
        if db_subscription in db_user.subscriptions:
            db_user.subscriptions.remove(db_subscription)
            db.commit()
            db.refresh(db_user)
        return db_user
    return None


def update_user(db: Session, user_id: int, user: schemas.UserUpdate) -> Optional[models.User]:
    db_user = db.query(models.User).filter(models.User.id == user_id).first()
    if db_user:
        for key, value in user.dict(exclude_unset=True).items():
            setattr(db_user, key, value)
        db.commit()
        db.refresh(db_user)
    return db_user

def update_user_email(db: Session, user_id: int, email: str):
    db_user = db.query(models.User).filter(models.User.id == user_id).first()
    email = models.Email(email=email, is_primary=True)
    db_user.emails = [email]
    db.commit()

def delete_user(db: Session, user_id: int) -> Optional[models.User]:
    db_user = db.query(models.User).filter(models.User.id == user_id).first()
    if db_user:
        db.delete(db_user)
        db.commit()
    return db_user

# 订阅相关CRUD操作
def create_subscription(db: Session, subscription: schemas.SubscriptionCreate) -> models.Subscription:
    db_subscription = models.Subscription(**subscription.dict())
    db.add(db_subscription)
    db.commit()
    db.refresh(db_subscription)
    return db_subscription

def get_subscription(db: Session, subscription_id: int) -> Optional[models.Subscription]:
    return db.query(models.Subscription).filter(models.Subscription.id == subscription_id).first()

def get_subscriptions(db: Session, skip: int = 0, limit: int = 100) -> List[models.Subscription]:
    return db.query(models.Subscription).offset(skip).limit(limit).all()

def update_subscription(db: Session, subscription_id: int, subscription: schemas.SubscriptionUpdate) -> Optional[
    models.Subscription]:
    db_subscription = db.query(models.Subscription).filter(models.Subscription.id == subscription_id).first()
    if db_subscription:
        for key, value in subscription.dict(exclude_unset=True).items():
            setattr(db_subscription, key, value)
        db.commit()
        db.refresh(db_subscription)
    return db_subscription

def delete_subscription(db: Session, subscription_id: int) -> Optional[models.Subscription]:
    db_subscription = db.query(models.Subscription).filter(models.Subscription.id == subscription_id).first()
    if db_subscription:
        db.delete(db_subscription)
        db.commit()
    return db_subscription

# 管理员邮箱相关CRUD操作
def create_admin_email(db: Session, admin_email: schemas.AdminEmailCreate) -> models.AdminEmail:
    db_admin_email = models.AdminEmail(**admin_email.dict())
    db.add(db_admin_email)
    db.commit()
    db.refresh(db_admin_email)
    return db_admin_email

def get_admin_email(db: Session, admin_email_id: int) -> Optional[models.AdminEmail]:
    return db.query(models.AdminEmail).filter(models.AdminEmail.id == admin_email_id).first()

def get_admin_emails(db: Session, skip: int = 0, limit: int = 100) -> List[models.AdminEmail]:
    return db.query(models.AdminEmail).offset(skip).limit(limit).all()

def update_admin_email(db: Session, admin_email_id: int, admin_email: schemas.AdminEmailUpdate) -> Optional[
    models.AdminEmail]:
    db_admin_email = db.query(models.AdminEmail).filter(models.AdminEmail.id == admin_email_id).first()
    if db_admin_email:
        for key, value in admin_email.dict(exclude_unset=True).items():
            setattr(db_admin_email, key, value)
        db.commit()
        db.refresh(db_admin_email)
    return db_admin_email

def delete_admin_email(db: Session, admin_email_id: int) -> Optional[models.AdminEmail]:
    db_admin_email = db.query(models.AdminEmail).filter(models.AdminEmail.id == admin_email_id).first()
    if db_admin_email:
        db.delete(db_admin_email)
        db.commit()
    return db_admin_email

# 邮件日志相关CRUD操作
def create_email_log(db: Session, email_log: schemas.EmailLogCreate) -> models.EmailLog:
    db_email_log = models.EmailLog(**email_log.dict())
    db.add(db_email_log)
    db.commit()
    db.refresh(db_email_log)
    return db_email_log

def get_email_log(db: Session, email_log_id: int) -> Optional[models.EmailLog]:
    return db.query(models.EmailLog).filter(models.EmailLog.id == email_log_id).first()

def get_email_logs(db: Session, skip: int = 0, limit: int = 100) -> List[models.EmailLog]:
    return db.query(models.EmailLog).offset(skip).limit(limit).all()

# API日志相关CRUD操作
def create_api_log(db: Session, api_log: schemas.APILogCreate) -> models.APILog:
    db_api_log = models.APILog(**api_log.dict())
    db.add(db_api_log)
    db.commit()
    db.refresh(db_api_log)
    return db_api_log

def get_api_log(db: Session, api_log_id: int) -> Optional[models.APILog]:
    return db.query(models.APILog).filter(models.APILog.id == api_log_id).first()

def get_api_logs(db: Session, skip: int = 0, limit: int = 100) -> List[models.APILog]:
    return db.query(models.APILog).offset(skip).limit(limit).all()

def authenticate_user(db: Session, username: str, password: str) -> Optional[models.User]:
    user = db.query(models.User).filter(models.User.username == username).first()
    if not user:
        return None
    if not verify_password(password, user.hashed_password):
        return None
    return user

def verify_password(plain_password: str, hashed_password: str) -> bool:
    from passlib.context import CryptContext

    pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
    return pwd_context.verify(plain_password, hashed_password)

def get_default_admin_email(db: Session) -> Optional[models.AdminEmail]:
    return db.query(models.AdminEmail).filter(models.AdminEmail.is_default == True).first()

def get_admin_email_by_id(db: Session, id) -> Optional[models.AdminEmail]:
    return db.query(models.AdminEmail).filter(models.AdminEmail.id == id).first()

def get_user_by_email(db: Session, email: str) -> Optional[models.User]:
    return db.query(models.User).join(models.Email).filter(models.Email.email == email).first()

def update_user_points(db: Session, user_id: int, points: int) -> Optional[models.User]:
    user = db.query(models.User).filter(models.User.id == user_id).first()
    if not user:
        return None
    user.points += points
    db.commit()
    db.refresh(user)
    return user

def create_point_log(db: Session, user_id, points_change, reason):
    db_api_log = models.PointLog(user_id=user_id, points_change=points_change, reason=reason)
    db.add(db_api_log)
    db.commit()
    db.refresh(db_api_log)
    return db_api_log